package com.timeDeal.service;



import com.alibaba.druid.util.StringUtils;
import com.timeDeal.bean.* ;
import com.timeDeal.util.DButil;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * Created by 叼爸爸 on 2016/9/4.
 */
public class UserService {

     public  boolean tijiaoGerenXinxi(UserBean ub){
         Connection cn = DButil.getConnection();
         String mysql="update user set zhenshixingming=? ,shouji=?,xingbie=?,nianling=?" +
                 ",youxiang=?,tongxundizhi=?,shenfenzhenghao=?,gerenxintiao=?,shengri=?"
                 + "where zhanghao=?"  ;
         String mysql3 ="SELECT yunxuid  FROM  user WHERE zhanghao=?" ;
         String mysql2 = "update yunxu   set zhenshixingming=? ,shouji=?,xingbie=?,nianling=?" +
                 ",youxiang=?,tongxundizhi=?,shenfenzhenghao=?,gerenxintiao=?,shengri=? " +
                 "where  yunxuid=?" ;
         PreparedStatement ps=null;
         ResultSet rs=null;
         try{
             int id = 0 ;
             ps=cn.prepareStatement(mysql3);
             ps.setString(1,ub.getZhanghao());
             rs = ps.executeQuery();
             if(rs.next()){
                  id = rs.getInt("yunxuid") ;
             }
             ps.clearParameters();
             ps=cn.prepareStatement(mysql);
             ps.setString(1,ub.getZhenshixingming());
             ps.setString(2,ub.getShouji());
             ps.setString(3,ub.getXingbie());
             ps.setInt(4,ub.getNianling());
             ps.setString(5,ub.getYouxiang());
             ps.setString(6,ub.getTongxundizhi());
             ps.setString(7,ub.getShenfenzhenghao());
             ps.setString(8,ub.getGerenxintiao());
             ps.setDate(9,ub.getShengri());
             ps.setString(10,ub.getZhanghao());
             ps .execute();
             ps.clearParameters();
             ps=cn.prepareStatement(mysql2);
             for (int i = 1; i <10 ; i++) {
                 ps.setString(i,ub.getGongkai()[i]);
             }
             ps.setInt(10,id);
             ps.execute();
             return  true ;
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             try {
                 if(ps!=null){
                     ps.close();
                 }
                 if(rs!=null){
                     rs.close();
                 }
                 cn.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
         return  false;}

    /**
     * 得到用户信息
     * @param id  用户id
     * @param i   需要的用户信息id
     * @return    String[]
     */
     public  String[] getDetail(int id,int i){
         Connection cn = DButil.getConnection();
         String mysql="select *   from  user where yonghuid=?"  ;
         PreparedStatement ps=null;
         String[] strings = new String[25] ;
         ResultSet rs=null;
         try{
             ps=cn.prepareStatement(mysql);
             ps.setInt(1,id);
             rs = ps.executeQuery();
             if(rs.next()&&i==25){
                     strings[0]=StringUtils.equals(rs.getString(15),null)?"":rs.getObject(15).toString();
                     strings[1]=StringUtils.equals(rs.getString(4),null)?"":rs.getObject(4).toString();
                     strings[2]=StringUtils.equals(rs.getString(5),null)?"":rs.getObject(5).toString();
                     strings[3]=StringUtils.equals(rs.getString(14),null)?"":rs.getObject(14).toString();
                     strings[4]=StringUtils.equals(rs.getString(12),null)?"":rs.getObject(12).toString();
                     strings[5]=StringUtils.equals(rs.getString(6),null)?"":rs.getObject(6).toString();
                     strings[6]=StringUtils.equals(rs.getString(7),null)?"":rs.getObject(7).toString();
                     strings[7]=StringUtils.equals(rs.getString(13),null)?"":rs.getObject(13).toString();
                     strings[8]=StringUtils.equals(rs.getString(11),null)?"":rs.getObject(11).toString();
                 return  strings;
             }
             else {
                 ps.clearParameters();
                 ps=cn.prepareStatement(mysql);
                 ps.setInt(1,id);
                 rs = ps.executeQuery();
                 if(rs.next()){
                  strings[0] = rs.getString(i).toString() ;}
                  return   strings ;
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             try {
                 if(ps!=null){
                     ps.close();
                 }
                 if(rs!=null){
                     rs.close();
                 }
                 cn.close();
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
        return  null;
     }
    public  String[] getDetail(String zhanghao,int i){
        Connection cn = DButil.getConnection();
        String mysql="select *   from  user where zhanghao=?"  ;
        PreparedStatement ps=null;
        String[] strings = new String[25] ;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,zhanghao);
            rs = ps.executeQuery();
            if(rs.next()&&i==25){
                strings[0]=rs.getString(15);
                strings[1]=rs.getObject(4).toString();
                strings[2]=rs.getObject(5).toString();
                strings[3]=rs.getObject(14).toString();
                strings[4]=rs.getObject(12).toString();
                strings[5]=rs.getObject(6).toString();
                strings[6]=rs.getObject(7).toString();
                strings[7]=rs.getObject(13).toString();
                strings[8]=rs.getObject(11).toString();
                return  strings;
            }
            else {
                ps.clearParameters();
                ps=cn.prepareStatement(mysql);
                ps.setString(1,zhanghao);
                rs = ps.executeQuery();
                if(rs.next()){
                    strings[0] = rs.getString(i).toString() ;}
                return   strings ;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
    public  boolean dologin(String ub,String  s){
        Connection cn = DButil.getConnection();
        String mysql="select  *  from  user where zhanghao=? and mima=?"  ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,ub);
            ps.setString(2,s);
            rs = ps.executeQuery();
            if(rs.next()){
                int i = rs.getInt(1);
                return  i>0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false;}
    public  String[] getYunXun(int id){
        Connection cn = DButil.getConnection();
        String mysql="SELECT yunxu.* FROM yunxu  INNER JOIN USER \n" +
                "ON user.yunxuid =yunxu.yunxuid WHERE user.yonghuid=?"  ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String[] s =new String[12];
        try{
            ps=cn.prepareStatement(mysql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                for (int i = 1; i <10 ; i++) {
                    s[i]=rs.getString(i+1);
                }
                return  s ;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
    public  boolean addAPinglun(PinglunBean pb, String leixing){
        Connection conn = DButil.getConnection() ;
        String mysql="insert into  pinglun   (pinglunid , xuqiufabuid,pinglunrenid,pinglunneirong,pinglunriqi )"
                +" values(?,?,?,?,?)";
        String mysq2="insert into  pinglun   (pinglunid , shijianchushouid,pinglunrenid,pinglunneirong,pinglunriqi )"
                +" values(?,?,?,?,?)";
        PreparedStatement ps=null;
        ResultSet rs=null;
        System.out.println(mysql);
        PinglunBean xb1 =pb ;
        try{
            if(StringUtils.equals(leixing,"xuqiufabu")){
            ps=conn.prepareStatement(mysql);
            ps.setInt(1,DButil.getZhuJianMax("pinglunid","pinglun"));
            ps.setInt(2,StringUtils.equals(leixing,"xuqiufabu")?xb1.getXuqiufabuid():xb1.getShijianchushouid());
            ps.setInt(3,xb1.getPinglunrenid());
            ps.setString(4,xb1.getPinglunneirong());
            ps.setDate(5,  xb1.getPinglunriqi());
            ps.execute();
            }else {
                ps=conn.prepareStatement(mysq2);
                ps.setInt(1,DButil.getZhuJianMax("pinglunid","pinglun"));
                ps.setInt(2,xb1.getShijianchushouid());
                ps.setInt(3,xb1.getPinglunrenid());
                ps.setString(4,xb1.getPinglunneirong());
                ps.setDate(5,  xb1.getPinglunriqi());
                ps.execute();
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false;
    }
    public String  getDetailById(int id){
        Connection cn = DButil.getConnection();
        String mysql="select  *   from  user where yonghuid="+ id  ;

        PreparedStatement ps=null;
        ResultSet rs=null;
        //System.out.println(mysql);
        try{
            ps=cn.prepareStatement(mysql);
            rs = ps.executeQuery();
            if(rs.next())
            return  rs.getString(2) ;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }

    /**
     * 根据id得到一个用户
     * @param id 用户id
     * @return   UserBean
     */
    public UserBean  getAuserByid(int id){
        Connection cn = DButil.getConnection();
        String mysql="select  *   from  user where yonghuid=? " ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        UserBean userBean =     new UserBean() ;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next())
            {
                userBean.setYonghuid(rs.getInt("yonghuid"));
                userBean.setYonghuming(rs.getString("yonghuming"));
                userBean.setXinyongdengji(rs.getString("xinyongdengji"));
                userBean.setZhuceriqi(new Date(Long.parseLong(rs.getString("zhuceriqi"))) );
                userBean.setJinggaocishu(rs.getInt("jinggaocishu"));
            }
            return   userBean ;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
	 public String  getDetailByYonghuming(String  nicheng){
        Connection cn = DButil.getConnection();
        String mysql="select  *   from  user where yonghuming="+ nicheng  ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            rs = ps.executeQuery();
            if(rs.next())
                return  rs.getString(2) ;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null;
    }
     /**
     * 用户注册
     * @param user
     * @return
     */
     public UserBean zhuce(UserBean user){
         Connection con= DButil.getConnection();
         System.out.println("SSSSSSSSSSSSSSSSSSSSSSSS");
         String sql2="insert into yunxu(yunxuid,zhenshixingming,shouji,xingbie,shengri,tongxundizhi,nianling,youxiang,shenfenzhenghao,gerenxintiao)values(?,?,?,?,?,?,?,?,?,?)";
         PreparedStatement ps=null;
         ResultSet rs=null;
         UserBean result=null;
         try{
             String sql="insert into user(yonghuid,yonghuming,zhanghao,mima,zhuceriqi,yunxuid,jinggaocishu,xinyongdengji)values(?,?,?,?,?,?,?,?)";
             ps=con.prepareStatement(sql);
             user.setYonghuid(DButil.getZhuJianMax("yonghuid","user"));
             ps.setInt(1,user.getYonghuid());
             ps.setString(2,user.getYonghuming());
             ps.setString(3,user.getZhanghao());
             ps.setString(4,user.getMima());
             ps.setString(5,String.valueOf(System.currentTimeMillis()));
             ps.setInt(6,user.getYonghuid());
             ps.setInt(7,0);
             ps.setString(8,"5");
             ps.execute();
             ps.clearParameters();
             ps= con.prepareStatement(sql2) ;
             ps.setInt(1,user.getYonghuid());
             ps.setString(2,"是");
             ps.setString(3,"是");
             ps.setString(4,"是");
             ps.setString(5,"是");
             ps.setString(6,"是");
             ps.setString(7,"是");
             ps.setString(8,"是");
             ps.setString(9,"是");
             ps.setString(10,"是");
             ps.execute() ;
             return user;
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             try{
                 if(rs!=null){
                     rs.close();
                 }
                 if(ps!=null){
                     ps.close();
                 }
                 con.close();
             }catch (Exception ex){
                 ex.printStackTrace();
             }
         }
         return result;

     }


    /**
     * 检查账号是否存在
     * @param zhanghao
     * @return
     */
    public boolean jianchazhanghao(String zhanghao)throws SQLException{
        String sql="";
        sql="SELECT*FROM user WHERE zhanghao=?";
        Connection con=DButil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=con.prepareStatement(sql);
            ps.setString(1,zhanghao);
            rs = ps.executeQuery();
            int count=0;
            if(rs.next()){
                count=rs.getInt(1);
            }
            return count>0;
        }finally {
            rs.close();
            ps.close();
            con.close();
        }

    }

    /**
     * 检查用户名是否存在
     * @param yonghuming
     * @return
     */
    public boolean jianchayonghuming(String yonghuming)throws SQLException{
        String sql="";
        sql="SELECT COUNT(*)FROM user WHERE yonghuming=?";
        Connection con=DButil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=con.prepareStatement(sql);
            ps.setString(1,yonghuming);
            rs = ps.executeQuery();
            int count=0;
            if(rs.next()){
                count=rs.getInt(1);
            }
            return count>0;
        }finally {
            rs.close();
            ps.close();
            con.close();
        }

    }

    /**
     * 检查密码是否一致
     * @param mima
     * @param chongfumima
     * @return
     */

    public boolean jianchamima(String mima,String chongfumima){
        return StringUtils.equals(mima,chongfumima);
    }

    /**
     * 注册业务逻辑
     * @param user
     * @param mimab
     * @return
     * @throws SQLException
     */
    public boolean doReg(UserBean user,String mimab) throws SQLException {
        //检查用户名是否存在
        System.out.println("111111111111111");
        if(jianchayonghuming(user.getYonghuming())){
            return false;
        }
        //检查账号是否存在
        System.out.println("222222222222222222");
        if(jianchazhanghao(user.getZhanghao())){
            return false;
        }
        //检查密码是否相等
        System.out.println("333333333333333333");
        if(!jianchamima(user.getMima(),mimab)){
            return false;
        }
        //用户名格式检查,不能为空，长度在0-30之间
        System.out.println("4444444444444444");
        if(user.getZhanghao().length()==0||user.getZhanghao().length()>30){
            return false;
        }
        System.out.println("5555555555555555555");
        //账号格式检查,不能为空，长度在0-30之间
        if(user.getZhanghao().length()==0||user.getZhanghao().length()>30){
            return false;
        }
        //检查密码长度
        if(user.getMima().length()<6){
            return false;
        }
        System.out.println("66666666666666");
        UserBean result = zhuce(user);
        return result!=null&&result.getYonghuid()>0;
    }
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJiaoyiById(int cu ,int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  xuqiufabu   where  zhuangtai=?  and (yifangid=? or jiafangid=?)"   ;
        String mysqljiaoyi="select  *    from  xuqiufabu   where zhuangtai=?  and (yifangid=? or jiafangid=?) limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        System.out.println(mysql);
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setString(1,"交易中");
            ps.setInt(2,id);
            ps.setInt(3,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljiaoyi);
            System.out.println(mysqljiaoyi);
            ps.setString(1,"交易中");
            ps.setInt(3,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                System.out.println(rs.getString("mingcheng"));
                XuqiufabuBean xuqiufabuBean = new XuqiufabuBean() ;
                xuqiufabuBean.setXuqiumingcheng(rs.getString("mingcheng"));
                xuqiufabuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xuqiufabuBean.setZhuangtai(rs.getString("zhuangtai"));
                xuqiufabuBean.setJiafangid(rs.getInt("jiafangid"));
                list.add(xuqiufabuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJiaoyiByIdandShijian(int cu ,int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  shijianchushou   where  zhuangtai=?  and (yifangid=? or jiafangid=?)"   ;
        String mysqljiaoyi="select  *    from  shijianchushou   where zhuangtai=?  and (yifangid=? or jiafangid=?) limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        System.out.println(mysql);
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setString(1,"交易中");
            ps.setInt(2,id);
            ps.setInt(3,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljiaoyi);
            ps.setString(1,"交易中");
            ps.setInt(3,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                ShijianchushouBean shijianchushouBean = new ShijianchushouBean() ;
                shijianchushouBean.setShijianchushoumingcheng(rs.getString("mingcheng"));
                shijianchushouBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                shijianchushouBean.setZhuangtai(rs.getString("zhuangtai"));
                shijianchushouBean.setJiafangid(rs.getInt("jiafangid"));
                list.add(shijianchushouBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;}
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJiaoyiByLishi(int cu ,int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  xuqiufabu   where  zhuangtai=?  and (yifangid=? or jiafangid=?)"   ;
        String mysqljiaoyi="select  *    from  xuqiufabu   where zhuangtai=?  and (yifangid=? or jiafangid=?) limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        System.out.println(mysql);
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2,id);
            ps.setInt(3,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljiaoyi);
            System.out.println(mysqljiaoyi);
            ps.setString(1,"交易完成");
            ps.setInt(3,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                System.out.println(rs.getString("mingcheng"));
                XuqiufabuBean xuqiufabuBean = new XuqiufabuBean() ;
                xuqiufabuBean.setXuqiumingcheng(rs.getString("mingcheng"));
                xuqiufabuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xuqiufabuBean.setJiafangid(rs.getInt("jiafangid"));
                xuqiufabuBean.setYifangid(rs.getInt("yifangid"));
                xuqiufabuBean.setJiafangpingjia(rs.getString("jiafangpingjia"));
                xuqiufabuBean.setYifangpingjia(rs.getString("yifangpingjia"));
                list.add(xuqiufabuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    /**
     * 根据浏览人id 和当前页码返回 与浏览人有关的交易完成项目
     * @param cu  当前页码
     * @param id  浏览人id
     * @return   PageList
     */
    public PageList getJiaoyiByLishiAndShijian(int cu ,int id){
        Connection connection = DButil.getConnection();
        int pagesize = PageList.pagesize ;
        String mysql="select count( *)   from  shijianchushou   where  zhuangtai=?  and (yifangid=? or jiafangid=?)"   ;
        String mysqljiaoyi="select  *    from  shijianchushou   where zhuangtai=?  and (yifangid=? or jiafangid=?) limit   " + (cu*pagesize-pagesize)+ " , "+ pagesize  ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        System.out.println(mysql);
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2,id);
            ps.setInt(3,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysqljiaoyi);
            ps.setString(1,"交易完成");
            ps.setInt(3,id);
            ps.setInt(2,id);
            rs=ps.executeQuery() ;
            while (rs.next()){
                ShijianchushouBean shijianchushouBean = new ShijianchushouBean() ;
                shijianchushouBean.setShijianchushoumingcheng(rs.getString("mingcheng"));
                shijianchushouBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                shijianchushouBean.setJiafangid(rs.getInt("jiafangid"));
                shijianchushouBean.setYifangid(rs.getInt("yifangid"));
                shijianchushouBean.setJiafangpingjia(rs.getString("jiafangpingjia"));
                shijianchushouBean.setYifangpingjia(rs.getString("yifangpingjia"));
                list.add(shijianchushouBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }

}
